package lab03.test14;

import javax.swing.plaf.nimbus.State;
import java.sql.*;

public class Test14 {
    public static void main(String[] args) {
//        14、首先将用户信息表中的数据在屏幕上显示出来，
//        然后插入一个记录，
//        最后将更新以后数据表中的数据在屏幕上显示出来。

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String sql = "create table t_userinfo(" +
                "usercode varchar(4) not null," +
                "username varchar(20)," +
                "password varchar(10)," +
                "email varchar(40)," +
                "primary key(usercode))";

        String sql0 = "insert into t_userinfo (usercode,username,password,email)" +
                "select '7101','宋晓梅','123456','xiaomei@yahoo.com.cn' UNION " +
                "select '7102','关松杰','654321','songjie@263.net' UNION " +
                "select '7103','李晓梅','123654','xiaomei@yahoo.com.cn' UNION " +
                "select '7104','张松岩','456123','songyan@yahoo.com.cn' UNION " +
                "select '7105','王亚杰','321456','yajie@hotmail.com' UNION " +
                "select '7106','曲小声','654321','xiaosheng@263.net' " ;



        String sql1 = "select * FROM t_userinfo";

        String sql2 = "insert into t_userinfo values('7107','周东杰','124563','zhoudong@yahao.com.cn')";

        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/java_2017?serverTimezone=UTC&characterEncoding=utf-8", "" +
                            "root", "123456");

            statement = connection.createStatement();
//            创建u_userinfo表
            statement.execute(sql);

//            插入数据
            statement.execute(sql0);

//            首先将用户信息表中的数据在屏幕上显示出来，
            statement.execute(sql1);
            resultSet = statement.getResultSet();

            while (resultSet.next()) {
                System.out.print(resultSet.getString(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.println(resultSet.getString(4));
            }
//
//
////            然后插入一个记录，
            statement.execute(sql2);

            System.out.println("--------------------------------------------");
//
//
////            最后将更新以后数据表中的数据在屏幕上显示出来。
            statement.execute(sql1);
            resultSet = statement.getResultSet();
            while (resultSet.next()) {
                System.out.print(resultSet.getString(1) + "\t");
                System.out.print(resultSet.getString(2) + "\t");
                System.out.print(resultSet.getString(3) + "\t");
                System.out.println(resultSet.getString(4));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
